Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# DECODE

The DECODE function compares one expression to one or more other expressions and, when the base expression is equal to a search expression, returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or `NA` when it is not.

Tip: To change the maximum size for new segments in an existing workspace, use the AW command with the SEGMENTSIZE keyword. To discover the current maximum size for new segments, use the AW function with the SEGMENTSIZE keyword.

Return Value

The data type of the first result argument.

Syntax

DECODE (expr , search, result [, search , result]... [, default])

Arguments

expr

  The expression to be searched. The function automatically converts expr to the data type of the first search value before comparing

search

  An expression to search for. The function automatically each search value to the data type of the first search value before comparing

result

  The expression to return when expression is equal to search.

default

  An expression to return when expression is not equal to search.

Notes

Order of Value Evaluation

The search, result, and default values can be derived from expressions. The function evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, the function never evaluates a search when a previous search is equal to expr.

Examples

Example 10-5 Decoding an ID Field

Assume that your analytic workspace contains the following objects. Note that the `inventory_location` formula uses the DECODE function to identify text values that correspond to the `INTEGER` values of `warehouse_id`.

    DESCRIBE
    DEFINE product_id DIMENSION TEXT
    DEFINE warehouse_id DIMENSION INTEGER
    DEFINE inventories VARIABLE DECIMAL <product_id warehouse_id>
    DEFINE inventory_location FORMULA TEXT <warehouse_id>
    EQ -
    DECODE (warehouse_id, 1, 'Southlake', 2, 'San Francisco', 4, 'Seattle', -
    'Non domestic')

    REPORT inventories
                         ---------------INVENTORIES---------------
                         ---------------PRODUCT_ID----------------
    WAREHOUSE_ID                 1770                 1775
    -------------------- -------------------- --------------------
                       1                30.63                79.02
                       2                71.49                55.83
                       3                88.71                68.02
                       4                86.27                41.86

    REPORT inventory_location
    WAREHOUSE_ID          INVENTORY_LOCATION
    -------------------- --------------------
                       1 Southlake
                       2 San Francisco
                       3 Non domestic
                       4 Seattle

The following reports illustrate how you can use `inventory_location` to display the decoded values of `warehouse_id` in a report rather than displaying the actual values of `warehouse_id`.

    LIMIT product_id to '1775'

    REPORT DOWN warehouse_id inventories
                         ----INVENTORIES-----
                         -----PRODUCT_ID-----
    WAREHOUSE_ID                 1775
    -------------------- --------------------
                       1                79.02
                       2                55.83
                       3                68.02
                       4                41.86

    REPORT DOWN inventory_location inventories
                         ----INVENTORIES-----
                         -----PRODUCT_ID-----
    INVENTORY_LOCATION           1775
    -------------------- --------------------
    Southlake                           79.02
    San Francisco                       55.83
    Non domestic                        68.02
    Seattle                             41.86

Example 10-6 DECODE with BITAND

Assume that you have the following objects with the reported values within your analytic workspace.

    DEFINE order_id DIMENSION TEXT
    DEFINE customer_id DIMENSION TEXT
    DEFINE order_customer COMPOSITE <order_id customer_id>
    DEFINE order_status VARIABLE NUMBER(2) <order_customer<order_id customer_id>>

    REPORT DOWN order_customer order_status
      ORDER_ID   CUSTOMER_ID  ORDER_STATUS
    ------------ ------------ ------------
    2458         101                  0.00
    2397         102                  1.00
    2454         103                  1.00
    2354         104                  0.00
    2358         105                  2.00
    2381         106                  3.00
    2440         107                  3.00
    2357         108                  5.00
    2394         109                  5.00
    2435         144                  6.00
    2455         145                  7.00
    2356         105                  5.00
    2360         107                  4.00

Assume that `order_status` is used as a bitmap where the first three bits hold information about the order and the other bits are always 0:

- The first bit is used for location information:

  0 = Post Office. This corresponds to INTEGER values of 0, 2, 4, and 6.

  1 = Warehouse. This corresponds to the numerical values of 1, 3, 5, and 7.

- The second bit is used for method:

  0 = Air. This corresponds to the numerical values of 0, 1, 4, and 5.

  1 = Ground. This corresponds to the numerical values of 2, 3, 6, and 7.

- The third bit is used for receipt:

  0 = Certified. This corresponds the numerical values of 0, 1, 2, and 3.

  1 =Insured. This corresponds to the numerical values of 4, 5, 6, and 7.

The following formulas use DECODE to substitute the text values for the bit values.

    DEFINE location FORMULA DECODE(BITAND(order_status, 1), 1, 'Warehouse', 'PostOffice')
    DEFINE method FORMULA DECODE(BITAND(order_status, 2), 2, 'Ground', 'Air')
    DEFINE receipt FORMULA DECODE(BITAND(order_status, 4), 4, 'Insured', 'Certified')

Now, you can issue a report to display the decoded values.

    REPORT DOWN order_customer order_status location method receipt

      ORDER_ID   CUSTOMER_ID  ORDER_STATUS   LOCATION      METHOD      RECEIPT
    ------------ ------------ ------------ ------------ ------------ ------------
    2458         101                  0.00 PostOffice   Air          Certified
    2397         102                  1.00 Warehouse    Air          Certified
    2454         103                  1.00 Warehouse    Air          Certified
    2354         104                  0.00 PostOffice   Air          Certified
    2358         105                  2.00 PostOffice   Ground       Certified
    2381         106                  3.00 Warehouse    Ground       Certified
    2440         107                  3.00 Warehouse    Ground       Certified
    2357         108                  5.00 Warehouse    Air          Insured
    2394         109                  5.00 Warehouse    Air          Insured
    2435         144                  6.00 PostOffice   Ground       Insured
    2455         145                  7.00 Warehouse    Ground       Insured
    2356         105                  5.00 Warehouse    Air          Insured
    2360         107                  4.00 PostOffice   Air          Insured